'''
Case Type： 故障&可靠性测试
Case Name： 数据库参数synchronous_commit = off时进行切换switchover，在新主机数据库建mot，插数据；
Create At: 2020/07/20
@zou_jialiang0505328114
Descption:
步骤 1.启动主备数据库，判断数据库状态；
步骤 2.确认synchronous_commit = off；
步骤 3.主备切换；
步骤 4.新主机建立mot内存表；
步骤 5.向新建表插入数据；
'''
import os
import unittest
import _thread
import time
from yat.test import macro
from yat.test import Node
from testcase.utils.CommonSH import *
from testcase.utils.Common import Common
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Deletaduit(unittest.TestCase):
    commonshpri = CommonSH('PrimaryDbUser')
    nodes_tuple = ('PrimaryDbUser', 'Standby1DbUser', 'Standby2DbUser')

    @RestartDbCluster(*nodes_tuple)
    def setUp(self):
        self.log = Logger()
        self.log.info('------------Opengauss_Reliability_Dbsys_Case153.py start------------')
        self.sourcecmd = f'source {macro.DB_ENV_PATH}'
        self.startCmd = f'source {macro.DB_ENV_PATH};gs_om -t start'
        self.stopCmd = f'source {macro.DB_ENV_PATH};gs_om -t stop'
        self.statusCmd = f'source {macro.DB_ENV_PATH};gs_om -t status --detail'
        self.rootNode = Node()
        self.dbUserNode1 = Node(node='PrimaryDbUser')
        self.dbUserNode = Node(node='PrimaryRoot')
        self.dbUserNode2 = Node(node='Standby1Root')
        self.dbUserNode3 = Node(node='Standby1DbUser')

    def test_startdb(self):
        switchovercmd = f'{self.sourcecmd};gs_ctl switchover -D {macro.DB_INSTANCE_PATH};gs_om -t refreshconf;'
        # 确认synchronous_commit = off；
        gucsetcmd1 = self.sourcecmd + 'gs_guc set - D ' + macro.DB_INSTANCE_PATH + ' - c \"synchronous_commit = off\"'
        gucsetcmd2 = self.sourcecmd + 'gs_guc set - D ' + macro.DB_INSTANCE_PATH + ' - c \"enable_incremental_checkpoint = off\"'
        self.log.info(gucsetcmd1)
        self.log.info(gucsetcmd2)
        self.dbUserNode1.sh(gucsetcmd1)
        self.dbUserNode1.sh(gucsetcmd2)
        self.dbUserNode1.sh(self.stopCmd)
        self.dbUserNode1.sh(self.startCmd)
        # 主备切换；
        self.dbUserNode3.sh(switchovercmd)
        # 新主机建立mot内存表，并插入数据；
        t = int(time.time())
        self.sql = '''\'set synchronous_commit=on;
           begin
           drop FOREIGN table if exists test''' + str(t) + ''';
           create FOREIGN table test''' + str(t) + '''(id int);
           for i in 1..9999 loop
           insert into test''' + str(t) + ''' values(i);
           end loop;
           end;
           commit;
           select count(1) from test''' + str(t) + ''';\'
           '''
        ddlcmd = self.sourcecmd + f';gsql -d {self.dbUserNode3.db_name} -p ' + self.dbUserNode3.db_port + ' -c ' + self.sql
        self.log.info(ddlcmd)
        msg3 = self.dbUserNode3.sh(ddlcmd).result()
        self.log.info(msg3)
        status = self.dbUserNode1.sh(self.statusCmd).result()
        self.log.info(status)
        self.assertIn('Normal', status)
        self.dbUserNode1.sh(switchovercmd)
        # 删除对应内存表
        self.sql1 = '''\'set synchronous_commit=on;
                  begin
                  drop FOREIGN table if exists test''' + str(t) + ''';
                  end;
                  '''
        ddlcmd1 = self.sourcecmd + f';gsql -d {self.dbUserNode3.db_name} -p ' + self.dbUserNode3.db_port + ' -c ' + self.sql1
        self.log.info(ddlcmd1)
        self.dbUserNode1.sh(ddlcmd1)

    def tearDown(self):
        # 恢复相关数据库参数，并重启数据库
        gucsetcmd3 = self.sourcecmd + 'gs_guc set - D ' + macro.DB_INSTANCE_PATH + ' - c \"synchronous_commit = on\"'
        gucsetcmd4 = self.sourcecmd + 'gs_guc set - D ' + macro.DB_INSTANCE_PATH + ' - c \"enable_incremental_checkpoint = on\"'
        self.dbUserNode1.sh(gucsetcmd3)
        self.dbUserNode1.sh(gucsetcmd4)
        self.dbUserNode1.sh(self.stopCmd)
        self.dbUserNode1.sh(self.startCmd)
        status = self.dbUserNode1.sh(self.statusCmd).result()
        if not status:
            raise Exception("db status is not true, please check!")
        else:
            pass
        self.log.info(
            '------------------------------------reliability_dbsys153.py finish------------------------------------')
